"""
财务数据处理模块
基于价值投资理念的A股资产负债表数据分析处理器
"""

import pandas as pd
import numpy as np
import sqlite3
from datetime import datetime, timedelta
from typing import Dict, List, Tuple, Optional
import akshare as ak


class BalanceSheetProcessor:
    """资产负债表数据处理器"""
    
    def __init__(self, db_path: str = "financial_data.db"):
        self.db_path = db_path
        self.init_database()
        
        # 科目分类映射表（基于资产负债表学习笔记）
        self.account_mapping = {
            # 流动资产
            'cash_equivalents': ['货币资金', '结算备付金', '拆出资金', '交易性金融资产', '衍生金融资产'],
            'receivables': ['应收票据', '应收账款', '应收款项融资'],
            'prepayments': ['预付款项'],
            'inventory': ['存货'],
            'other_current': ['应收保费', '应收分保款项', '其他应收款', '买入返售金融资产', 
                            '合同资产', '持有待售资产', '一年内到期的非流动资产', '其他流动资产'],
            
            # 非流动资产
            'long_term_investments': ['长期股权投资', '其他权益工具投资', '其他非流动金融资产'],
            'fixed_assets': ['固定资产', '在建工程'],
            'intangible_goodwill': ['无形资产', '商誉'],
            'other_non_current': ['发放贷款和垫资', '债权投资', '其他债权投资', '长期应收款',
                                '投资性房地产', '生物性生物资产', '油气资产', '使用权资产',
                                '开发支出', '长期待摊费用', '递延所得税资产', '其他非流动资产'],
            
            # 流动负债
            'short_term_debt': ['短期借款', '向中央银行借款', '拆入资金', '衍生金融负债', 
                              '一年内到期的非流动负债'],
            'payables': ['应付票据', '应付账款', '其他应付款'],
            'advance_receipts': ['预收款项', '合同负债'],
            'accrued_expenses': ['应付职工薪酬', '应交税费'],
            'other_current_liab': ['卖出回购金融资产款', '吸收存款及同行存放', '代理买卖证券款',
                                 '代理承销证券款', '应付手续费及佣金', '应付分保账款', 
                                 '持有待售负债', '其他流动负债'],
            
            # 非流动负债
            'long_term_debt': ['长期借款', '应付债券'],
            'other_non_current_liab': ['保险合同准备金', '租赁负债', '长期应付款', '长期应付职工薪酬',
                                     '预计负债', '递延收益', '递延所得税负债', '其他流动负债']
        }
    
    def init_database(self):
        """初始化数据库"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # 创建股票基本信息表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS stocks (
                stock_code TEXT PRIMARY KEY,
                stock_name TEXT,
                market TEXT,
                industry TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 创建资产负债表数据表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS balance_sheet (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                stock_code TEXT,
                report_date DATE,
                report_type TEXT,
                account_name TEXT,
                account_value REAL,
                category TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (stock_code) REFERENCES stocks(stock_code)
            )
        ''')
        
        # 创建分析结果缓存表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS analysis_cache (
                stock_code TEXT,
                analysis_date DATE,
                value_score REAL,
                asset_quality_score REAL,
                safety_score REAL,
                growth_score REAL,
                key_metrics TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (stock_code, analysis_date)
            )
        ''')
        
        # 创建索引
        cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_balance_sheet_query 
            ON balance_sheet(stock_code, report_date, category)
        ''')
        
        conn.commit()
        conn.close()
    
    def get_balance_sheet_data(self, stock_code: str, force_refresh: bool = False) -> pd.DataFrame:
        """获取资产负债表数据"""
        # 检查本地缓存
        if not force_refresh:
            cached_data = self._get_cached_data(stock_code)
            if cached_data is not None and self._is_data_fresh(cached_data):
                return cached_data
        
        try:
            # 调用AKShare API获取数据
            raw_data = ak.stock_balance_sheet_by_report_em(symbol=stock_code)
            
            # 数据清洗和验证
            cleaned_data = self._clean_and_validate(raw_data)
            
            # 保存到本地缓存
            self._save_to_cache(stock_code, cleaned_data)
            
            return cleaned_data
            
        except Exception as e:
            print(f"获取数据失败: {e}")
            # 返回缓存数据（如果有）
            return self._get_cached_data(stock_code)
    
    def categorize_accounts(self, raw_data: pd.DataFrame) -> pd.DataFrame:
        """对财务科目进行分类"""
        categorized_data = raw_data.copy()
        categorized_data['category'] = '未分类'
        
        for category, accounts in self.account_mapping.items():
            for account in accounts:
                mask = categorized_data['科目'].str.contains(account, na=False)
                categorized_data.loc[mask, 'category'] = category
        
        return categorized_data
    
    def calculate_key_metrics(self, categorized_data: pd.DataFrame) -> Dict:
        """计算关键财务指标"""
        latest_data = categorized_data.iloc[0]  # 最新一期数据
        
        # 基础数据提取
        total_assets = latest_data.get('总资产', 0)
        total_liabilities = latest_data.get('总负债', 0)
        total_equity = latest_data.get('总权益', 0)
        current_assets = latest_data.get('流动资产合计', 0)
        current_liabilities = latest_data.get('流动负债合计', 0)
        
        # 计算关键指标
        metrics = {
            'asset_liability_ratio': total_liabilities / total_assets if total_assets > 0 else 0,
            'current_ratio': current_assets / current_liabilities if current_liabilities > 0 else 0,
            'equity_ratio': total_equity / total_assets if total_assets > 0 else 0,
            'total_assets': total_assets,
            'total_liabilities': total_liabilities,
            'total_equity': total_equity
        }
        
        return metrics
    
    def calculate_value_score(self, key_metrics: Dict, historical_data: pd.DataFrame) -> Dict:
        """计算价值投资评分"""
        
        # 资产质量评分 (0-100分)
        asset_quality = self._calculate_asset_quality_score(key_metrics, historical_data)
        
        # 财务安全性评分 (0-100分)
        safety_score = self._calculate_safety_score(key_metrics)
        
        # 成长性评分 (0-100分)
        growth_score = self._calculate_growth_score(historical_data)
        
        # 综合评分（加权平均）
        weights = {'asset_quality': 0.4, 'safety': 0.35, 'growth': 0.25}
        comprehensive_score = (
            asset_quality * weights['asset_quality'] +
            safety_score * weights['safety'] +
            growth_score * weights['growth']
        )
        
        return {
            'comprehensive_score': comprehensive_score,
            'asset_quality_score': asset_quality,
            'safety_score': safety_score,
            'growth_score': growth_score,
            'grade': self._get_investment_grade(comprehensive_score)
        }
    
    def _calculate_asset_quality_score(self, metrics: Dict, historical_data: pd.DataFrame) -> float:
        """计算资产质量评分"""
        score = 0
        
        # 资产负债率评分 (30分)
        debt_ratio = metrics.get('asset_liability_ratio', 0)
        if debt_ratio < 0.3:
            score += 30
        elif debt_ratio < 0.5:
            score += 30 - (debt_ratio - 0.3) * 150
        
        # 流动比率评分 (30分)
        current_ratio = metrics.get('current_ratio', 0)
        if current_ratio > 2:
            score += 30
        elif current_ratio > 1:
            score += current_ratio * 15
        
        # 权益比例评分 (40分)
        equity_ratio = metrics.get('equity_ratio', 0)
        score += min(40, equity_ratio * 80)
        
        return min(100, score)
    
    def _calculate_safety_score(self, metrics: Dict) -> float:
        """计算财务安全性评分"""
        score = 0
        
        # 资产负债率安全性 (50分)
        debt_ratio = metrics.get('asset_liability_ratio', 0)
        if debt_ratio < 0.3:
            score += 50
        elif debt_ratio < 0.6:
            score += 50 - (debt_ratio - 0.3) * 166.67
        
        # 流动性安全性 (50分)
        current_ratio = metrics.get('current_ratio', 0)
        if current_ratio > 1.5:
            score += 50
        elif current_ratio > 1:
            score += (current_ratio - 1) * 100
        
        return min(100, score)
    
    def _calculate_growth_score(self, historical_data: pd.DataFrame) -> float:
        """计算成长性评分"""
        if len(historical_data) < 2:
            return 50  # 数据不足，给予中等评分
        
        # 计算总资产增长率
        latest_assets = historical_data.iloc[0].get('总资产', 0)
        previous_assets = historical_data.iloc[1].get('总资产', 0)
        
        if previous_assets > 0:
            growth_rate = (latest_assets - previous_assets) / previous_assets
            # 增长率转换为评分 (0-100分)
            if growth_rate > 0.2:  # 增长率>20%
                return 100
            elif growth_rate > 0.1:  # 增长率10-20%
                return 80
            elif growth_rate > 0.05:  # 增长率5-10%
                return 60
            elif growth_rate > 0:  # 增长率0-5%
                return 40
            else:  # 负增长
                return max(0, 40 + growth_rate * 200)
        
        return 50
    
    def _get_investment_grade(self, score: float) -> str:
        """根据评分获取投资等级"""
        if score >= 90:
            return 'A+'
        elif score >= 80:
            return 'A'
        elif score >= 70:
            return 'B+'
        elif score >= 60:
            return 'B'
        elif score >= 50:
            return 'C'
        else:
            return 'D'
    
    def _get_cached_data(self, stock_code: str) -> Optional[pd.DataFrame]:
        """获取缓存数据"""
        try:
            conn = sqlite3.connect(self.db_path)
            query = '''
                SELECT * FROM balance_sheet 
                WHERE stock_code = ? 
                ORDER BY report_date DESC
            '''
            data = pd.read_sql_query(query, conn, params=(stock_code,))
            conn.close()
            
            return data if not data.empty else None
        except:
            return None
    
    def _is_data_fresh(self, data: pd.DataFrame) -> bool:
        """检查数据是否新鲜"""
        if data.empty:
            return False
        
        latest_update = pd.to_datetime(data['created_at'].iloc[0])
        return (datetime.now() - latest_update).days < 7
    
    def _clean_and_validate(self, raw_data: pd.DataFrame) -> pd.DataFrame:
        """数据清洗和验证"""
        # 移除空值行
        cleaned_data = raw_data.dropna(subset=['报告期'])
        
        # 数据类型转换
        numeric_columns = [col for col in cleaned_data.columns if '金额' in col or '比例' in col]
        for col in numeric_columns:
            cleaned_data[col] = pd.to_numeric(cleaned_data[col], errors='coerce')
        
        # 日期格式标准化
        cleaned_data['报告期'] = pd.to_datetime(cleaned_data['报告期'])
        
        return cleaned_data
    
    def _save_to_cache(self, stock_code: str, data: pd.DataFrame):
        """保存数据到缓存"""
        try:
            conn = sqlite3.connect(self.db_path)
            
            # 删除旧数据
            cursor = conn.cursor()
            cursor.execute('DELETE FROM balance_sheet WHERE stock_code = ?', (stock_code,))
            
            # 保存新数据
            for _, row in data.iterrows():
                cursor.execute('''
                    INSERT INTO balance_sheet 
                    (stock_code, report_date, account_name, account_value, category)
                    VALUES (?, ?, ?, ?, ?)
                ''', (stock_code, row.get('报告期'), row.get('科目'), 
                     row.get('金额'), row.get('category', '未分类')))
            
            conn.commit()
            conn.close()
        except Exception as e:
            print(f"保存缓存失败: {e}")


# 使用示例
if __name__ == "__main__":
    processor = BalanceSheetProcessor()
    
    # 获取平安银行(000001)的资产负债表数据
    data = processor.get_balance_sheet_data("000001")
    
    if data is not None:
        # 科目分类
        categorized = processor.categorize_accounts(data)
        
        # 计算关键指标
        metrics = processor.calculate_key_metrics(categorized)
        
        # 计算价值投资评分
        scores = processor.calculate_value_score(metrics, categorized)
        
        print(f"综合评分: {scores['comprehensive_score']:.1f}")
        print(f"投资等级: {scores['grade']}")
        print(f"资产质量: {scores['asset_quality_score']:.1f}")
        print(f"财务安全: {scores['safety_score']:.1f}")
        print(f"成长性: {scores['growth_score']:.1f}")
